//
//  SQLiteManager.swift
//  Project03-Weibo-swift
//
//  Created by w on 16/2/2.
//  Copyright © 2016年 w. All rights reserved.
//

import UIKit
//import FMDataBase

//TODO: 创建数据库并连接；创建表；执行查询语句

private let dbName = "cxstatus.db"
class SQLiteManager {
    
    //单例对象
    static let sharedManager: SQLiteManager = SQLiteManager()
    
    /// 持有一个全局访问点
    var dbQueue: FMDatabaseQueue!
    
    //将创建方法私有化
    private init() {
        
        //数据库地址
        let dbpath = (NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(dbName)
        
        print(dbpath)  // For test
        
        dbQueue = FMDatabaseQueue(path: dbpath)  //如果数据库文件不存在 则创建
        createDBTable()   //创建表
    }
    
}
// MARK: - 建表, 执行查询语句等操作
extension SQLiteManager {
    
    //建表
    private func createDBTable() {
        
        let commandPath = NSBundle.mainBundle().pathForResource("db", ofType: "sql")!
        
        //将sql语句加载
        let command = try! String(contentsOfFile: commandPath)
        
        ///  开始执行建表语句
        dbQueue.inDatabase { (db) -> Void in
            
            let result = db.executeStatements(command)
            if result {
                print("建表成功")
            } else {
                print("建表失败")
            }
        }
        
    }
    
    //查询
    func execOperation(sql: String) -> [[String: AnyObject]] {
        
        var res = [[String: AnyObject]]()
        
        dbQueue.inDatabase { (db) -> Void in
            
            let resSet = db.executeQuery(sql, withArgumentsInArray: nil)
            
            //结果集不空
            while resSet.next() {
                
                var dict = [String: AnyObject]()   //收集每一个元组的各个属性
                
                //遍历每一列 获取属性名和属性值
                for i in 0..<resSet.columnCount() {
                    
                    let name = resSet.columnNameForIndex(i)
                    let value = resSet.objectForColumnName(name)
                    dict[name] = value
                }
                res.append(dict)
            }
        }
        return res
    }
    
}